IBM Datastage to Oracle SQL / DBT Converter

The converter allows performing "one-click"- or "batch"-migration of IBM Datastage job(s) to Oracle SQL or DBT.

The converter algorithm goes through dataflows and converts each Stage into an SQL CTE (Common Table Expression), then transforms the resulting SQL/PLSQL into a DBT model.

Convertable

• lookup-stages (+multi joins), sparse lookups
• join-stages (+multi joins)
• filters
• oracle readers
  • CTE reorganization
  • columns order substitution
  • "on-demand" type casting
  • collect database credentials and store them in SQL as comments
• oracle writers to „insert into“ or „dbt-model“-format (optional with last columns-casting)
• "in-front-of" shared containers
• sorters (+ using partition by)
• deduplicate-stages (+ deduplicate by keys using partition by)
• transform-stages
  • convert "Datastage Basic" expressions & functions to Oracle SQL
  • "topologic-sort" variable dependencies
  • support of DS-window-functions like LastRowInGroup()
  • complex functions like EReplace
  • loops
  • self referenced (recorsion) variables. See Emulating Recursive Computations in SQL Using MATCH_RECOGNIZE or Window Functions
  • store "Datastage Basic" expressions in SQL as comments
  • etc
• hash-caculation converter
• aggregators
• pivoting
• increment generator to SQL-sequence
• funnel-stages
• copy-stages (+ aliases)
• main annotation + Datastage-binds-comments
• collect job parameters and prepare for DBT
• etc

Extras

• automatic check of SQL compilation
• generation of regressions-tests assets in DBT

Covered products:

• IBM DataStage 11.7
• Oracle
• DBT

As part of phasing out IBM products and migrating to a new ETL/ELT technology stack, I developed the converter shown above.